import sys
import time
from datetime import datetime, timedelta

import tushare as ts

from a00_stock_auto.a01_utils import StockUtil
from a00_stock_auto.a01_utils.DB_MySQL import SqlHelper

# 'e67b88d7877fec4e4c3587ef4a8cae671c2dc8812f870b4874c37a9c' # iamcychan@163.com token
ts.set_token('e67b88d7877fec4e4c3587ef4a8cae671c2dc8812f870b4874c37a9c')# 199

pro = ts.pro_api()
# SQL插入语句
insert_sql = f"INSERT INTO ts_stock_daily (ts_code, trade_date, open_today, high, low, close_today, pre_close, price_change,  pct_chg,vol, amount) VALUES (%s, %s, %s, %s,%s, %s, %s, %s,%s, %s, %s)"

# 20241008 此接口无权限 Exception: 抱歉，您没有访问该接口的权限，请联系管理员
# df = pro.trade_cal(exchange='SSE', start_date='20240101', end_date='20241231')
# print(df)

# # 获取股票列表 SSE 上交所
# df = pro.stock_basic(exchange='', list_status='L', fields='ts_code,symbol,name,area,industry,list_date')
# for row in df.itertuples(index=False):  # 遍历DataFrame中的每一行
#     print(row)
#     # SqlHelper.get_instance().execute_curd(sql, row)
#     print("正在插入数据:", row)  # 输出正在插入的数据


# 获取指定股票的交易记录
# df1 = pro.daily(ts_code='300251.SZ,300603.SZ', start_date='20240729', end_date='20240730')
# print(df1)
# # 每一列的操作方式
# # for col in df1.columns:  # 遍历DataFrame中的每一列
# #     print(f"Column Name: {col}")
# #     # 把数字中的 "," 替换掉（删除）
# #     print(df1[col])
# # 每一行的数据
# sql = f"INSERT INTO ts_stock_daily (ts_code, trade_date, open_today, high, low, close_today, pre_close, price_change,  pct_chg,vol, amount) VALUES (%s, %s, %s, %s,%s, %s, %s, %s,%s, %s, %s)"  # SQL插入语句
#
# for row in df1.itertuples(index=False):  # 遍历DataFrame中的每一行
#     print(row)
#     # SqlHelper.get_instance().execute_curd(sql, row)
#     print("正在插入数据:", row)  # 输出正在插入的数据

# 获取某一天的交易记录

def fetch_stock_by_calendar(calendar_date):
    """
    # 根据传入的交易日期进行查询交易记录
    :param calendar_date:
    :return:
    """
    for result in calendar_date:
        print(result[1])
        df = pro.daily(trade_date=result[1].strftime('%Y%m%d'))
        # 将查询到的每一行的数据存入数据库
        for row in df.itertuples(index=False):  # 遍历DataFrame中的每一行
            # print(row)
            SqlHelper.get_instance().execute_curd(insert_sql, row)
            print("正在插入数据:", row)  # 输出正在插入的数据
        time.sleep(2)


def start_end_stock_list(start_date, end_date):
    """
    获取某一段时间的交易记录
    :return:
    """
    while start_date < end_date:
        df = pro.daily(trade_date=start_date.strftime('%Y%m%d'))
        for row in df.itertuples(index=False):  # 遍历DataFrame中的每一行
            # print(row)
            SqlHelper.get_instance().execute_curd(insert_sql, row)
            print("正在插入数据:", row)  # 输出正在插入的数据
        print(start_date.strftime('%Y%m%d'))
        start_date = start_date + timedelta(days=1)
        time.sleep(2)


def stock_price_today():
    """
    获取当天的交易数据
    :return:
    """
    df = pro.daily(trade_date=datetime.now().date().strftime('%Y%m%d'))
    for row in df.itertuples(index=False):  # 遍历DataFrame中的每一行
        print(row)
        SqlHelper.get_instance().execute_curd(insert_sql, row)
        print("正在插入数据:", row)  # 输出正在插入的数据
    print(datetime.now().date().strftime('%Y%m%d'))


if __name__ == '__main__':
    # start_date = datetime(2024, 10, 8).date()
    # end_date = datetime(2024, 10, 9).date()
    #    stock_price_today()
    print(sys.path)
    # # 1、获取某一年度的交易日历，然后根据交易日历获取每一日的交易数据插入数据库
    results = StockUtil.fetch_trade_day_before()
    fetch_stock_by_calendar(results)
    # # 2、获取当天的交易数据导入数据库
    stock_price_today()